package com.lee.poi.hssf.official;

import com.graphbuilder.geom.PointFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.awt.*;
import java.io.*;
import java.util.Calendar;
import java.util.Date;

@SuppressWarnings("all")
public class ExcelTest {

    /**
     * 创建简单的sheet、cell
     *
     * @throws IOException
     */
    @Test
    public void test1() throws IOException {
        Workbook wb = new HSSFWorkbook();


        //创建sheet页
        Sheet sheet = wb.createSheet("李新1");

        //创建一行
        Row row = sheet.createRow(0);

        Cell cell = row.createCell(0);
        cell.setCellValue("李新");


        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    }


    /**
     * 创建Date类型的cell
     *
     * @throws IOException
     */
    @Test
    public void test2() throws IOException {
        Workbook wb = new HSSFWorkbook();


        //创建sheet页
        Sheet sheet = wb.createSheet("new Sheet");

        //创建一行
        Row row = sheet.createRow(0);

        //无格式的Date
        Cell cell = row.createCell(0);
        cell.setCellValue(new Date());

        //样式
        CellStyle cellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));

        cell = row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cellStyle);

        cell = row.createCell(2);
        cell.setCellValue(Calendar.getInstance());
        cell.setCellStyle(cellStyle);


        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    }


    /**
     * 存储不用的类型
     *
     * @throws IOException
     */
    @Test
    public void test3() throws IOException {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = sheet.createRow(2);
        row.createCell(0).setCellValue(1.1);
        row.createCell(1).setCellValue(new Date());
        row.createCell(2).setCellValue(Calendar.getInstance());
        row.createCell(3).setCellValue("a string");
        row.createCell(4).setCellValue(true);
        row.createCell(5).setCellType(CellType.ERROR);

        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
    }


    /**
     * excel 读取
     * an InputStream requires more memory as it has to buffer the whole file.
     */
    public void test4() throws IOException {
        // Use a file
        Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

        // Use an InputStream, needs more memory
//        Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));

    }


    /**
     * 使用poi必须完成整个生命周期。包括完成后关闭文件
     *
     * @throws IOException
     */
    @Test
    public void test5() throws IOException {
        // HSSFWorkbook, File
        POIFSFileSystem fs = new POIFSFileSystem(new File("workbook.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
        fs.close();

        // HSSFWorkbook, InputStream, needs more memory
        POIFSFileSystem fs2 = new POIFSFileSystem(new FileInputStream("workbook.xls"));
        HSSFWorkbook wb2 = new HSSFWorkbook(fs2.getRoot(), true);

        // XSSFWorkbook, File
//        OPCPackage pkg3 = OPCPackage.open(new File("file.xlsx"));
//        XSSFWorkbook wb = new XSSFWorkbook(pkg);
//        pkg.close();

        // XSSFWorkbook, InputStream, needs more memory
//        OPCPackage pkg = OPCPackage.open(myInputStream);
//        XSSFWorkbook wb = new XSSFWorkbook(pkg);
//        pkg.close();

    }


    /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     * @param valign the vertical alignment for the cell.
     */
    private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
        Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }


    /**
     * 各种对其、居中
     *
     * @throws IOException
     */
    @Test
    public void test6() throws IOException {
        Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(2);
        row.setHeightInPoints(30);
        createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
        createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
        createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
        createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
        createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
        createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
        createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {
            wb.write(fileOut);
        }
        wb.close();
    }


    /**
     * 边框
     */
    @Test
    public void test7() throws IOException {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("new sheet");
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(1);
        cell.setCellValue(4);

        CellStyle style = wb.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(BorderStyle.MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);

        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
            wb.write(fileOut);
        }
        wb.close();
    }


    /**
     * excel读取
     */
    @Test
    public void test8() throws IOException {
        File file = new File("开机活跃用户数.xls");
        POIFSFileSystem fs = new POIFSFileSystem(file);
        HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);

        for (Sheet sheet : wb) {
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.STRING) {
                        System.out.print(cell.getStringCellValue() + "  ");
                    } else {
                        System.out.print(cell.getNumericCellValue() + "  ");
                    }
                }
                System.out.println();
            }
            System.out.println();
        }

        fs.close();
    }



}
